<?php
namespace api\service;

use Yii;

class ShopService
{
    /**
     * pub_user跟edu_user 连表获取用户的信息
     * @param $uid
     * @return array|false
     */
    public function userPubMin($uid)
    {
        $sql = "SELECT pub.id AS pub_id, pub.openid AS pub_openid, pub.nickname, pub.headimgurl, pub.unionid,mini.id AS mini_id, mini.openid AS mini_openid, mini.recharge  FROM pub_user AS pub LEFT JOIN edu_user  AS mini ON pub.unionid = mini.unionid WHERE mini.mall_is = 1 AND pub.id = {$uid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 统计未处理的提现数
     * @param $uid
     * @return false|null|string
     */
    public function unDeal($uid)
    {
        $sql = "SELECT COUNT(*) FROM mall_appear WHERE mid = {$uid} AND status = 1";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找每个申请列表
     * @param $map
     * @param $page
     * @param $pre_page
     * @return array
     */
    public function appearAmount($map, $page, $pre_page)
    {
        $count = $this->appearCount($map);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $offset = ($page - 1) * $pre_page;
            $total_page = ceil( $count / $pre_page);
            $sql = "SELECT  id, openid, uid, amount, status, reason, create_time, finish_time, check_id, order_num, tid, mid  FROM mall_appear WHERE {$map} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page} ";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            foreach ( $res as $key => $val) {
                $sql = "SELECT id, nickname, mall_rname FROM edu_user WHERE id = {$val['uid']}";
                $user = Yii::$app->db->createCommand($sql)->queryOne();
                $val['name'] = $user['nickname'] . '-' . $user['mall_rname'];
                $res[$key] = $val;
            }
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];
        }
        return $data;

    }

    /**
     * 统计商家有关的提现申请
     * @param $map
     * @return false|null|string
     */
    private function appearCount($map)
    {
        $sql = "SELECT COUNT(*) FROM mall_appear WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找其中一条申请提现记录
     * @param $order_num
     * @return array|false
     */
    public function appearDetail($order_num)
    {
        $sql = "SELECT id, openid, uid, amount, status, reason, create_time, finish_time, check_id, order_num, tid, mid FROM mall_appear WHERE order_num = '{$order_num}'";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查询一条未处理的订单
     * @param $map
     * @return array|false
     */
    public function appearDetailOne($map)
    {
        $sql = "SELECT  id, openid, uid, amount, status, reason, create_time, finish_time, check_id, order_num, tid, mid  FROM mall_appear WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 用户提现进去冻结金额跟累加提现金额
     * @param $openid
     * @param $amount
     * @return int
     */
    public function userDelAmount($openid, $amount)
    {
        $sql = "UPDATE edu_user SET mall_put_money = mall_put_money - {$amount}, mall_appear_fee = mall_appear_fee + {$amount} WHERE  openid = '{$openid}'";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * @param $id
     * @param $tid
     * @param $uid
     * @param $message
     * @return int
     */
    public function appearChange($id, $tid, $uid,$message)
    {
        $time = time();
        $sql = "UPDATE mall_appear SET status = 2,tid = {$tid},finish_time = {$time}, check_id = {$uid}, message = '{$message}' WHERE id = {$id}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找商家信息
     * @param $mid
     * @return array|false
     */
    public function shopOne($mid)
    {
        $sql = "SELECT id,recharge, is_red FROM edu_user WHERE id = {$mid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 修改为拒绝状态
     * @param $id
     * @param $uid
     * @param $reason
     * @return int
     */
    public function appearChangeDel($id, $uid, $reason)
    {
        $time = time();
        $sql = "UPDATE mall_appear SET status = 3,finish_time = {$time}, check_id = {$uid},reason = '{$reason}' WHERE id = {$id}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 拒绝给用户加回去
     * @param $openid
     * @param $amount
     * @return int
     */
    public function userAddAmount($openid, $amount)
    {
        $sql = "UPDATE edu_user SET mall_put_money = mall_put_money - {$amount}, mall_total_fee = mall_total_fee + {$amount} WHERE  openid = '{$openid}'";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 零钱提现给商家减去
     * @param $mid
     * @param $amount
     * @return int
     */
    public function shopDeaAmount($mid, $amount)
    {
        $sql = "UPDATE edu_user SET recharge = recharge - {$amount} WHERE  id = {$mid}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找店铺里客户信息
     * @param $map
     * @param $page
     * @param $pre_page
     * @return array
     */
    public function shopCustoms($map, $page, $pre_page)
    {
        $count = $this->customsCount($map);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $offset = ($page - 1) * $pre_page;
            $total_page = ceil($count / $pre_page);
            $sql = "SELECT mall_total_fee, mall_appear_fee, id,nickname FROM edu_user WHERE {$map} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();

            $resData = [];
            foreach ($res as $_key => $_val) {
                $sqlWait = "SELECT SUM(amount) FROM mall_bill WHERE uid = {$_val['id']} AND status = 2";
                $waitProfit = Yii::$app->db->createCommand($sqlWait)->queryScalar();
                $waitProfit = $waitProfit ? $waitProfit : 0;
                $_val['wait_profit'] = $waitProfit;
                $resData[$_key] = $_val;
            }
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];

        }
        return $data;
    }

    /**
     * 计算店铺下的客户数量
     * @param $map
     * @return false|null|string
     */
    private function customsCount($map)
    {
        $sql = "SELECT COUNT(*) FROM edu_user WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    public function userProfitDetail($andWhere)
    {
        $sql = " SELECT out_trade_no, status, direct, direct_text, amount, create_time, finish_time, uid, sid, mid FROM mall_bill WHERE {$andWhere} ORDER BY create_time DESC LIMIT 10000";
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * 获取一条订单详情
     * @param $where
     * @return array|false
     */
    public function orderOne($where)
    {
        $sql = "SELECT mid, uid, openid,out_trade_no, total_fee,name, phone,create_time, status,address FROM mall_order WHERE {$where}";
        $res = Yii::$app->db->createCommand($sql)->queryOne();
        if ($res) {
            $sqlSku = "SELECT o.count, p.id, o.price, o.value ,p.img, p.title FROM mall_order_list AS o LEFT JOIN mall_product AS p ON o.pid = p.id WHERE o.out_trade_no = '{$res['out_trade_no']}'";
            $sku = Yii::$app->db->createCommand($sqlSku)->queryAll();
            $res['list'] =  $sku;
        }
        return $res;
    }

    /**
     * 获取拼团订单里面的产品信息
     * @param $where
     * @return array|false
     */
    public function orderOneAssemble($where)
    {
        $sql = "SELECT mid, uid, openid,out_trade_no, total_fee,name, phone,create_time, status,address FROM mall_assemble_order WHERE {$where}";
        $res = Yii::$app->db->createCommand($sql)->queryOne();
        if ($res) {
            //$sqlSku = "SELECT o.count, p.id, o.price, o.value ,p.img, p.title FROM mall_order_list AS o LEFT JOIN mall_product AS p ON o.pid = p.id WHERE out_trade_no = '{$res['out_trade_no']}''";
            $sqlSku = "SELECT o.count, p.id, o.price, o.value ,p.img, p.title FROM mall_assemble_order AS o LEFT JOIN mall_assemble_product AS p ON o.pid = p.id WHERE out_trade_no = '{$res['out_trade_no']}''";
            $sku = Yii::$app->db->createCommand($sqlSku)->queryAll();
            $res['list'] =  $sku;
        }
        return $res;
    }

    /**
     * 获取订单
     * @param $out_trade_no
     * @return array|false
     */
    public function orderMessage($out_trade_no)
    {
        $sql = "SELECT * FROM mall_order WHERE out_trade_no = '{$out_trade_no}'";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 红包提现给用户
     * @param $id
     * @param $tid
     * @param $mid
     * @param $message
     * @param $mch_billno
     * @return int
     */
    public function appearSuccess($id, $tid, $mid,$message, $mch_billno)
    {
        $time = time();
        $sql = "UPDATE mall_appear SET status = 2,tid = {$tid},finish_time = {$time}, mch_billno = '{$mch_billno}', check_id = {$mid}, message = '{$message}' WHERE id = {$id}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找商家
     * @param $uid
     * @return array|false
     */
    public function userMin($uid)
    {
        $sql = "SELECT mini.id AS mini_id, mini.openid AS mini_openid, mini.recharge  FROM  edu_user  AS mini WHERE mini.mall_is = 1 AND mini.id = {$uid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找用户是否存在
     * @param $uid
     * @param $mid
     * @return array|false
     */
    public function userAuth($uid, $mid)
    {
        $sql = "SELECT id, mall_id, roles, openid FROM edu_user WHERE id = {$uid} AND mall_id = {$mid} AND roles = 1";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找角色roles = 1的用户
     * @param $where
     * @return array|false
     */
    public function financeOne($where)
    {
        $sql = "SELECT id, mall_id, roles, openid FROM edu_user WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 将用户update为roles=1 为财务
     * @param $map
     * @param $andWhere
     * @return int
     */
    public function financeUpdate($map, $andWhere)
    {
        $sql = "UPDATE edu_user set {$map} WHERE {$andWhere}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 插入一条财务记录
     * @param $mapWhere
     * @return int
     */
    public function financeRecord($mapWhere)
    {
        return Yii::$app->db->createCommand()->insert('mall_finance_record', $mapWhere)->execute();
    }

    /**
     * 获取一条财务信息
     * @param $map
     * @return array|false
     */
    public function financeGet($map)
    {
        $sql = "SELECT u.id, u.phone, u.openid, u.roles, u.mall_id, f.wechat FROM edu_user As u LEFT JOIN mall_finance_record AS f ON u.id = f.uid WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找用户是否是财务身份
     * @param $uid
     * @return array|false
     */
    public function isRoles($uid)
    {
        $sql = "SELECT mall_id, id, roles FROM edu_user WHERE id = {$uid} AND roles = 1";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }
}